package nl.ViewerServer.Database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;

import nl.ViewerServer.Exceptions.ParameterException;
import nl.ViewerServer.ServerManager.DataManager.Objects.Parameter;
import nl.ViewerServer.ServerManager.DataManager.Objects.Source;
import nl.ViewerServer.ServerManager.DataManager.Objects.Visualization;
import nl.ViewerServer.ServerManager.DataManager.PipelineObjects.Pipeline;
import nl.ViewerServer.ServerManager.DataManager.PipelineObjects.PipelineObject;
import nl.ViewerServer.ServerManager.DataManager.PipelineObjects.PipelineOutput;
import nl.ViewerServer.ServerManager.DataManager.PipelineObjects.PipelineSource;

public class SessionManagerDatabaseConnection extends DatabaseConnection {
   // we cannot use a preparedStatement because of variable parameters
   // protected PreparedStatement getPipelineOutputs;
   // 
   protected String            getPipelineOutputs;
   protected PreparedStatement getPipelineVisualization;
   protected PreparedStatement getPipelineSource;
   protected PreparedStatement getPipelineObjectParameters;
   protected PreparedStatement getPipelineObjectParameterValues;
   protected PreparedStatement insertParameter;
   protected PreparedStatement getCustomParameter;

   protected PreparedStatement getSessionId;
   protected PreparedStatement getJobId;

   public SessionManagerDatabaseConnection() throws ClassNotFoundException, SQLException {
      super();
   }

   protected void prepareStatements() throws SQLException {
      insertParameter = conn.prepareStatement(
            "INSERT INTO `parameter` (`objectType`, `objectId`, `parameterName`, `parameterType`, `isTuple`, `parameterLength`, `defaultValue`, `isUserViewable`, `isUserModifiable`, `parameterGetter`, `parameterSetter`, `setterType`) VALUES('CUSTOM', -1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            PreparedStatement.RETURN_GENERATED_KEYS );
      getCustomParameter = conn.prepareStatement( "SELECT * FROM `parameter` WHERE objectType = 'CUSTOM' AND `parameterName` = ?" );

      // cannot use this preparedStatement, see comment above
      // getPipelineOutputs = conn.prepareStatement( "SELECT * FROM `pipelineOutput` WHERE `outputId` IN ( ? )" );
      getPipelineOutputs = "SELECT * FROM `pipelineOutput` WHERE `outputId` IN ";
      getPipelineVisualization = conn.prepareStatement( "SELECT * FROM `pipelineVisualization` WHERE `visualizationId` = ?" );
      getPipelineSource = conn.prepareStatement( "SELECT * FROM `pipelineSource` WHERE `sourceId` = ?" );
      getPipelineObjectParameters = conn.prepareStatement( "SELECT * FROM `parameter` WHERE `objectType` = ? AND (`objectId` = -1 OR `objectId` = ?)" );
      getPipelineObjectParameterValues = conn.prepareStatement( "SELECT * FROM `parameterValue` pv, `parameter` p WHERE pv.`parameterId` = p.`parameterId` AND p.`objectType` = ? AND (p.`objectId` = -1 OR p.`objectId` = ?) AND (`modalityId` IS NULL OR `modalityId` = ?) AND (`visualizationId` IS NULL or `visualizationId` = ?)" );

      getSessionId = conn.prepareStatement( "SELECT get_next_session_id()" );
      getJobId = conn.prepareStatement( "SELECT get_next_job_id()" );
   }

   /**
    * This method will fetch a pipelineSource object from the database, based on a specified source, modality and
    * visualization. This method will also retrieve all the parameters (and additional parameter values) for the object.
    * 
    * @param source
    * @param modalityId
    * @param visualizationId
    * @return
    * @throws SQLException
    */

   public synchronized PipelineSource createPipelineSource( Source source, int modalityId, int visualizationId )
         throws SQLException {
      getPipelineSource.clearParameters();
      getPipelineSource.setInt( 1, source.sourceId );
      ResultSet rs = getPipelineSource.executeQuery();
      PipelineSource result = null;
      if( rs.next() ) {
         result = new PipelineSource( source.sourceId, rs.getString( "name" ), rs.getString( "script" ) );
      }
      rs.close();

      if( result == null ) {
         throw new SQLException(
               "PipelineSource object for modality " + modalityId + " and visualization " + visualizationId + " does not exist" );
      }

      getPipelineObjectParameters( result );

      getPipelineObjectParameterValues( result, modalityId, visualizationId );

      return result;
   }

   /**
    * This will retrieve a pipeline (visualization) from the database, given some visualization and modality. This
    * method will also retrieve all the parameters (and additional parameter values) for the object.
    * 
    * @param visualization
    * @param modalityId
    * @return
    * @throws SQLException
    */

   public synchronized Pipeline createPipeline( Visualization visualization, int modalityId ) throws SQLException {
      getPipelineVisualization.clearParameters();
      getPipelineVisualization.setInt( 1, visualization.visualizationId );
      ResultSet rs = getPipelineVisualization.executeQuery();
      Pipeline result = null;
      if( rs.next() ) {
         result = new Pipeline( visualization.visualizationId, rs.getString( "name" ), rs.getString( "script" ) );
      }
      rs.close();

      if( result == null ) {
         throw new SQLException(
               "Pipeline object for modality " + modalityId + " and visualization " + visualization.visualizationId + " does not exist" );
      }

      getPipelineObjectParameters( result );

      getPipelineObjectParameterValues( result, modalityId, visualization.visualizationId );

      return result;
   }

   /**
    * This method will create the last object(s) of the visualization pipeline, the outputs, given a set of outputIds
    * and a modality/visualization. This method will also retrieve all the parameters (and additional parameter values)
    * for the object.
    * 
    * @param outputIds
    * @param modalityId
    * @param visualizationId
    * @return
    * @throws SQLException
    */

   public synchronized HashMap<String, PipelineOutput> createPipelineOutputs( Set<Integer> outputIds, int modalityId,
         int visualizationId ) throws SQLException {
      // get all output id's
      String commaSeparatedList = null;
      Iterator<Integer> ids = outputIds.iterator();
      while( ids.hasNext() ) {
         if( commaSeparatedList != null ) {
            commaSeparatedList += "," + ids.next();
         } else {
            commaSeparatedList = "" + ids.next();
         }
      }
      String query = getPipelineOutputs + "(" + commaSeparatedList + ")";

      // getPipelineOutputs.clearParameters();
      // getPipelineOutputs.setString( 1, commaSeparatedList );
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery( query );

      HashMap<String, PipelineOutput> result = new HashMap<String, PipelineOutput>();

      while( rs.next() ) {
         PipelineOutput output = new PipelineOutput( rs.getInt( "outputId" ), rs.getString( "name" ),
               rs.getString( "script" ), rs.getString( "outputDataType" ) );
         result.put( rs.getString( "name" ), output );
         getPipelineObjectParameters( output );
         getPipelineObjectParameterValues( output, modalityId, visualizationId );
      }
      rs.close();
      st.close();

      return result;
   }

   private void getPipelineObjectParameters( PipelineObject object ) throws SQLException {
      String objectType = object.getObjectType();
      if( objectType == null ) {
         log.error( "Unknown pipeline object to get parameters for" + object );
         return;
      }

      getPipelineObjectParameters.clearParameters();
      getPipelineObjectParameters.setString( 1, objectType );
      getPipelineObjectParameters.setInt( 2, object.objectId );

      ResultSet rs = getPipelineObjectParameters.executeQuery();

      while( rs.next() ) {
         try {
            if( rs.getString( "parameterName" ).equalsIgnoreCase( "StoreLocation" ) == false ) {
               object.addParameter( rs.getInt( "parameterId" ), rs.getString( "parameterName" ),
                     rs.getString( "parameterType" ), rs.getBoolean( "isTuple" ), rs.getInt( "parameterLength" ),
                     rs.getString( "defaultValue" ), rs.getBoolean( "isUserViewable" ),
                     rs.getBoolean( "isUserModifiable" ), rs.getString( "parameterGetter" ),
                     rs.getString( "parameterSetter" ), rs.getString( "setterType" ),
                     rs.getBoolean( "requiredForSynchronization" ) );

            } else {
               log.warn( "Could not add parameter to pipeline object, 'StoreLocation' is a reserved parameter name" );
            }
         }
         catch( ParameterException e ) {
            log.warn( "Could not add parameter '" + rs.getString( "parameterName" ) + "' to pipeline object", e );
         }
      }
      rs.close();

   }

   private void getPipelineObjectParameterValues( PipelineObject object, int modalityId, int visualizationId )
         throws SQLException {
      String objectType = object.getObjectType();
      if( objectType == null ) {
         log.error( "Unknown pipeline object to get parameters for: " + object );
         return;
      }

      getPipelineObjectParameterValues.clearParameters();
      getPipelineObjectParameterValues.setString( 1, objectType );
      getPipelineObjectParameterValues.setInt( 2, object.objectId );
      getPipelineObjectParameterValues.setInt( 3, modalityId );
      getPipelineObjectParameterValues.setInt( 4, visualizationId );

      ResultSet rs = getPipelineObjectParameterValues.executeQuery();

      while( rs.next() ) {
         try {
            object.addParameterValue( rs.getInt( "parameterId" ), rs.getString( "parameterValue" ) );
            log.info( "Set parameter value for " + object.name + "/ " + rs.getInt( "parameterId" ) + "/" + rs.getString( "parameterValue" ) );
         }
         catch( ParameterException e ) {
            log.warn( "addParameterValue for '" + rs.getString( "parameterName" ) + "' failed: ", e );
         }
      }
      rs.close();
   }

   /**
    * This method will create (or fetch) a custom parameter in (from) the database. This method is used to create a
    * unique parameterId for a custom parameter which is introduced in the server code, and which needs a unique
    * parameter id to be able to store the parameter as lookmark parameter.
    * 
    * @param p
    * @return
    * @throws SQLException
    */
   public synchronized int registerCustomSessionParameter( Parameter p ) throws SQLException {
      // don't access the database then the parameter id has already been set
      if( p.parameterId >= 0 )
         return p.parameterId;

      getCustomParameter.clearParameters();
      getCustomParameter.setString( 1, p.parameterName );

      ResultSet rs = getCustomParameter.executeQuery();
      if( rs.next() ) {
         // This solution is far from perfect, we should actually check if the provided parameter type/length/etc
         // matches with the row in the database. However, for example the alignedSourcesLabels parameter which
         // is introduced by a Session can result in a different parameter length, based on the amount of aligned
         // modalities present. From the point of view that we just need a unique parameter id, to store our parameter
         // in the lookmark_parameterValue table, so that it can actually be retrieved by a client, more or less
         // 'justifies' the current solution

         // parameter exists, get the parameter id
         p.parameterId = rs.getInt( "parameterId" );
         return p.parameterId;
      }
      rs.close();

      // otherwise: create a new parameter based on the provided Parameter object
      insertParameter.clearParameters();
      insertParameter.setString( 1, p.parameterName );
      insertParameter.setString( 2, p.getParameterType().toString() );
      insertParameter.setInt( 3, ( p.isTuple() ? 1 : 0 ) );
      insertParameter.setInt( 4, p.getParameterLength() );
      insertParameter.setString( 5, p.getPythonRepresentation() );
      insertParameter.setInt( 6, ( p.isUserViewable() ? 1 : 0 ) );
      insertParameter.setInt( 7, ( p.isUserModifiable() ? 1 : 0 ) );
      insertParameter.setString( 8, p.getParameterGetter() );
      insertParameter.setString( 9, p.getParameterSetter() );
      insertParameter.setString( 10, p.getSetterType().toString() );

      insertParameter.execute();

      rs = insertParameter.getGeneratedKeys();
      if( !rs.next() )
         throw new SQLException( "Insert of custom session parameter returned no generated parameterId" );

      p.parameterId = rs.getInt( 1 );
      rs.close();

      return p.parameterId;

   }

   public synchronized long getNextSessionId() throws SQLException {
      ResultSet rs = getSessionId.executeQuery();
      if( !rs.next() )
         throw new SQLException( "No results received for request of new sessionId" );

      long id = rs.getLong( 1 );
      rs.close();

      return id;
   }

   public synchronized long getNextJobId() throws SQLException {
      ResultSet rs = getJobId.executeQuery();
      if( !rs.next() )
         throw new SQLException( "No results received for request of new sessionId" );

      long id = rs.getLong( 1 );
      rs.close();

      return id;

   }

}
